package fixtures;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import Messages.MessageExtendLending;
import Messages.MessageLendBook;
import Messages.MessageNewTelephone;
import server.SQLConnection;
import fit.ActionFixture;

public class ExtendLending extends ActionFixture{
	public SQLConnection conn;
	PreparedStatement ps,ps3;
	private String id,copy,x,idwant,copywant; 
	public Statement stmt;
	
	
	public MessageLendBook lend=new MessageLendBook();
	public MessageExtendLending ex = new MessageExtendLending();
	
	public String lendbook() throws SQLException, IOException {
			
			stmt = conn.getConnection().createStatement();
			java.sql.ResultSet rs1= stmt.executeQuery("select * from db1.subscriber ");
			rs1.next();
			id=rs1.getString(1);
			
			ps=conn.getConnection().prepareStatement("SELECT * FROM db1.subscriber WHERE ID= ? And Status= ?;");
			ps.setString(1,id);
			ps.setString(2,"0");
			ResultSet rs = ps.executeQuery(); 
			
			stmt = conn.getConnection().createStatement();
			java.sql.ResultSet rs2= stmt.executeQuery("select db1.bookscopies.CopyId from db1.bookscopies,db1.books where db1.bookscopies.BID=db1.books.ID and db1.books.HighlyWanted='No' and db1.bookscopies.copyid not in (SELECT db1.lending.CopyId FROM db1.lending where db1.lending.lend='yes' ) ;");
			rs2.next();
			copy=rs2.getString(1);
			
		
		    String[] str2= {"123", id,copy};
		    x = lend.checklend(str2);
		    return x;
		    
		    
			}
		
	public String changeReturnDate() throws SQLException   {
		stmt = conn.getConnection().createStatement();
		java.sql.ResultSet rs3= stmt.executeQuery("SELECT db1.lending.StartDate,db1.lending.EndDate FROM db1.lending where db1.lending.lend='yes' and db1.lending.CopyId='"+copy+"';");
		
		
		
		rs3.next();
		String start , end ; 
		start=rs3.getString(1);
		end=start;
		stmt.executeUpdate("update db1.lending set EndDate ='"+end+"'  where CopyId ='"+copy+"'");
		
		java.sql.ResultSet rs4= stmt.executeQuery("SELECT db1.lending.StartDate,db1.lending.EndDate FROM db1.lending where db1.lending.lend='yes' and db1.lending.CopyId='"+copy+"';");
		rs4.next();
		if (rs4.getString(1).equals(rs4.getString(2)))
		return "return date updated";
		
		else  return "return date  not updated";
	}

	
	public String extendlend (){
		
		String[] str = { "extend" , copy , id } ;
		
		  String x = ex.extendlend(str);
		  
		  return x;
		
	}
	
	
	public String LendBookThatAllReadyLended() throws SQLException, IOException {
		
		stmt = conn.getConnection().createStatement();
		java.sql.ResultSet rs1= stmt.executeQuery("select * from db1.subscriber ");
		rs1.next();
		id=rs1.getString(1);
		
		ps=conn.getConnection().prepareStatement("SELECT * FROM db1.subscriber WHERE ID= ? And Status= ?;");
		ps.setString(1,id);
		ps.setString(2,"0");
		ResultSet rs = ps.executeQuery(); 
		
		stmt = conn.getConnection().createStatement();
		java.sql.ResultSet rs2= stmt.executeQuery("select db1.bookscopies.CopyId from db1.bookscopies where db1.bookscopies.copyid not in (SELECT db1.lending.CopyId FROM db1.lending where db1.lending.lend='no') ;");
		rs2.next();
		copy=rs2.getString(1);
		
	
	    String[] str2= {"123", id,copy};
	    x = lend.checklend(str2);
	    return x;
	    
	    
		}
	public String BookDoesntExist() throws SQLException, IOException {
		
		stmt = conn.getConnection().createStatement();
		java.sql.ResultSet rs1= stmt.executeQuery("select * from db1.subscriber ");
		rs1.next();
		id=rs1.getString(1);
		
		ps=conn.getConnection().prepareStatement("SELECT * FROM db1.subscriber WHERE ID= ? And Status= ?;");
		ps.setString(1,id);
		ps.setString(2,"0");
		ResultSet rs = ps.executeQuery(); 
		
		
		java.sql.ResultSet copyid= stmt.executeQuery("select db1.bookscopies.CopyId from db1.bookscopies ");
		
		int row1 = 0;
		while(copyid.next()){
			int temp = copyid.getInt(1);
				if(row1>temp)
					row1=row1;
					else{
					row1=temp;
					}
		
		}
		String row2= ""+(row1+1); 
	
	    String[] str2= {"123", id,row2};
	    x = lend.checklend(str2);
	    return x;
	    
	    
		}
	public String lendwantedbook() throws SQLException, IOException {
		
		stmt = conn.getConnection().createStatement();
		java.sql.ResultSet rs1= stmt.executeQuery("select * from db1.subscriber ");
		rs1.next();
		idwant=rs1.getString(1);
		
		ps=conn.getConnection().prepareStatement("SELECT * FROM db1.subscriber WHERE ID= ? And Status= ?;");
		ps.setString(1,idwant);
		ps.setString(2,"0");
		ResultSet rs = ps.executeQuery(); 
		
		stmt = conn.getConnection().createStatement();
		java.sql.ResultSet rs2= stmt.executeQuery("select db1.bookscopies.CopyId from db1.bookscopies,db1.books where db1.bookscopies.BID=db1.books.ID and db1.books.HighlyWanted='Yes' and db1.bookscopies.copyid not in (SELECT db1.lending.CopyId FROM db1.lending where db1.lending.lend='yes' ) ;");
		rs2.next();
		copywant=rs2.getString(1);
		
	
	    String[] str2= {"123", idwant,copywant};
	    x = lend.checklend(str2);
	    return x;
	    
	    
		}
	

	public String extendwanted (){
		
		String[] str = { "extendwant" , copywant , idwant } ;
		
		  String x = ex.extendlend(str);
		  
		  return x;
		
	}

public boolean startconnnection(){
	conn = new SQLConnection();
	return true;
	
}




}